/********************************************/
  // Creation History
  // --------------------------------------------------------------
  //	Company:			HTHK
  //	NAME:				WeeklyTSSearch.java
  //    Title   :
  //	FUNCTION:
  //	BY:					Beyes Xia
  //	DATE:				2004-4-15 11:20
  // --------------------------------------------------------------
/*********************************************/

package tss.weeklytimesheet;

import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.log4j.Logger;

import java.io.*;
import java.util.*;
import java.sql.*;

import com.ffpc.sql.*;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

import tss.common.DBServlet2;

public class WeeklyTSSearch extends DBServlet2
{
	private Logger logger = Logger.getLogger(WeeklyTSSearch.class);
	@Override
	protected String perform(	HttpServletRequest request,
								HttpServletResponse response,
								StrutsMessage message	)
    throws  ServletException,
            IOException,
            SQLException,
            DataException,
            Exception
	{
		
		
		String staff_no = request.getParameter("staff_no");
		String date_from = request.getParameter("date_from");
		String date_to = request.getParameter("date_to");

		logger.debug("staff_no="+staff_no);
		logger.debug("date_from="+date_from);
		logger.debug("date_to="+date_to);
	  
	  	String begin_date = get_sunday(date_from);
	  	String end_date = get_sunday(date_to); 
	  	
		logger.debug("begin_date:"+begin_date);
		logger.debug("end_date:"+end_date);
		/**********add try{}catch{}finally{} 2013-07-05 by leosen*******/
		Statement stmt=null;
		ResultSet rsWeek = null;
		ResultSet rsSpend = null;
		try{
			stmt = this.getConnection().createStatement();
					
			int weekCount = 0;
			String sqlWeek = "select to_date('"+end_date+"','YYYY-MM-DD')-to_date('"+begin_date+"','YYYY-MM-DD') from dual";
		  	rsWeek = stmt.executeQuery(sqlWeek);
			if(rsWeek.next())
			{
				weekCount = rsWeek.getInt(1)/7+1;
			}
			rsWeek.close();
			logger.debug("weekCount:"+weekCount);

			ArrayList week = new ArrayList();
			String tempWeek = begin_date;
			for(int i=0;i<weekCount;i++)
			{
				week.add(tempWeek);
				tempWeek = add_date(tempWeek,7);
			}
			logger.debug("week:"+week);

			Hashtable spend = new Hashtable();
			String sqlSpend ="Select to_char(start_date,'YYYY/MM/DD'),"+
							"sum(nvl(sunDay_spend,0)+nvl(monday_spend,0)+nvl(tuesday_spend,0)"+
							"+nvl(wednesday_spend,0)+nvl(thursday_spend,0)+nvl(friday_spend,0)"+
							"+nvl(saturday_spend,0)) From tss.tss_t_timesheet Where 1=1 ";					
				sqlSpend += "And staff_no = '"+staff_no+"' ";
				sqlSpend += "And start_date >= to_date('"+begin_date+"','YYYY-MM-DD') "+
							"And start_date <= to_date('"+end_date+"','YYYY-MM-DD') "+
							"Group By staff_no,start_date "+
							"Order By staff_no";
			rsSpend = stmt.executeQuery(sqlSpend);
			while(rsSpend.next())
			{
				spend.put(rsSpend.getString(1),rsSpend.getString(2));
			}
			logger.debug("sqlSpend:"+sqlSpend);
			logger.debug("spend ht:"+spend.toString());
			request.setAttribute("week",week);
			request.setAttribute("spend",spend);
		} finally {
			JdbcUtil.release(stmt, rsWeek);
			JdbcUtil.release(stmt, rsSpend);
		}
		
		
		return "tss/weeklytimesheet/WeeklyTSList.jsp";
  }
  	private String get_sunday(String date)
	{
		Calendar temp=Calendar.getInstance();
		String sunday="";
		int year=Integer.parseInt(date.substring(0,4));
		int month=Integer.parseInt(date.substring(5,7));
		int day=Integer.parseInt(date.substring(8,10));
		temp.set(year,month-1,day);
		temp.add(Calendar.DATE,1-temp.get(Calendar.DAY_OF_WEEK));
		String monthStr="";
		String dayStr="";
		monthStr=(temp.get(Calendar.MONTH)>=9?""+(temp.get(Calendar.MONTH)+1):"0"+(temp.get(Calendar.MONTH)+1));
		dayStr=(temp.get(Calendar.DAY_OF_MONTH)>=10?""+temp.get(Calendar.DAY_OF_MONTH):"0"+temp.get(Calendar.DAY_OF_MONTH));
		sunday=""+temp.get(Calendar.YEAR)+"/"+monthStr+"/"+dayStr;
		logger.debug("sunday="+sunday);
		return sunday;
	}
	private String add_date(String date,int n)
	{
		Calendar temp=Calendar.getInstance();
		String sunday="";
		int year=Integer.parseInt(date.substring(0,4));
		int month=Integer.parseInt(date.substring(5,7));
		int day=Integer.parseInt(date.substring(8,10));
		temp.set(year,month-1,day);
		temp.add(Calendar.DATE,n);
		String monthStr="";
		String dayStr="";
		monthStr=(temp.get(Calendar.MONTH)>=9?""+(temp.get(Calendar.MONTH)+1):"0"+(temp.get(Calendar.MONTH)+1));
		dayStr=(temp.get(Calendar.DAY_OF_MONTH)>=10?""+temp.get(Calendar.DAY_OF_MONTH):"0"+temp.get(Calendar.DAY_OF_MONTH));
		sunday=""+temp.get(Calendar.YEAR)+"/"+monthStr+"/"+dayStr;
		return sunday;
	}	
}


